﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Reflection;

namespace DBLibrary
{
    /// <summary>
    /// 继承这个抽象类的唯一理由是：需要直接操作sql语句
    /// </summary>
    public abstract class DbBase : IDisposable
    {
        private DbProviderFactory _factory;
        protected IDbConnection _dbConn;
        private string _connectionString;
        private bool _keepConnection;
        private bool _realDelete = false;
        private const string _isDeletedField = "IsDeleted";
        /// <summary>
        /// 是否保持连接
        /// </summary>
        public bool KeepConnection
        {
            get { return _keepConnection; }
            set { _keepConnection = value; }
        }

        public bool RealDelete { get { return _realDelete; } set { _realDelete = value; } }

        public DbBase(IConnectionConfiguration cm)
        {
            if (string.IsNullOrEmpty(cm.ConnectionString))
            {
                throw new ArgumentException("Connectin string cann't be null or empty.");
            }
            if (string.IsNullOrEmpty(cm.DbType))
            {
                throw new ArgumentException("Db type string cann't be null or empty.");
            }
            _keepConnection = false;
            _connectionString = cm.ConnectionString;
            _factory = DbProviderFactories.GetFactory(cm.DbType);
        }

        public void Open()
        {
            if (_dbConn == null || _dbConn.State == ConnectionState.Closed)
            {
                _dbConn = _factory.CreateConnection();
                _dbConn.ConnectionString = _connectionString;
                _dbConn.Open();
            }
        }

        public void Close(bool forcedClose = true)
        {
            if (forcedClose)
            {
                RealClose();
            }
            else
            {
                if (!_keepConnection)
                {
                    RealClose();
                }
            }
        }

        protected void RealClose()
        {
            if (_dbConn != null)
            {
                try
                {
                    _dbConn.Close();
                }
                finally
                {
                    _dbConn = null;
                }
            }
        }

        public virtual bool Exist<T>(params DBParam[] keys)
        {
            if (keys == null || keys.Length == 0)
            {
                throw new ArgumentException("Exist method : table key(s) cann't be null or empty.");
            }
            bool isExist = false;
            try
            {
                Open();
                SchemaCacheItem item = DBMapSchemaCache.GetCacheItem(typeof(T));
                string tableName = item.TableMap.TableName;
                using (IDbCommand cmd = _dbConn.CreateCommand())
                {
                    string whereSql = string.Empty;
                    foreach (DBParam pair in keys)
                    {
                        string columnName = pair.Key;
                        ColumnMapAttribute colMap = item.GetColumnMap(columnName);
                        whereSql += string.Format("{0}=@{0} and ", colMap.ColumnName);
                        cmd.Parameters.Add(CreateParam(colMap.ColumnName, pair.Value));
                    }
                    whereSql += " IsDeleted=@IsDeleted";
                    cmd.Parameters.Add(CreateParam("IsDeleted", false));
                    cmd.CommandText = string.Format("if exists(select '1' from {0} where {1}) select 1 else select 0", tableName, whereSql);
                    isExist = int.Parse(cmd.ExecuteScalar().ToString()) == 1;
                }
            }
            finally
            {
                Close(false);
            }
            return isExist;
        }

        public virtual List<T> Select<T>(params DBParam[] keys)
        {
            List<T> list = new List<T>();
            try
            {
                Open();
                SchemaCacheItem item = DBMapSchemaCache.GetCacheItem(typeof(T));
                string tableName = item.TableMap.TableName;
                using (IDbCommand cmd = _dbConn.CreateCommand())
                {
                    string whereSql = string.Empty;
                    foreach (DBParam pair in keys)
                    {
                        string columnName = pair.Key;
                        ColumnMapAttribute colMap = item.GetColumnMap(columnName);
                        whereSql += string.Format("{0}=@{0} and ", colMap.ColumnName);
                        cmd.Parameters.Add(CreateParam(colMap.ColumnName,pair.Value));
                    }
                    whereSql += " IsDeleted=@IsDeleted";
                    cmd.Parameters.Add(CreateParam("IsDeleted", false));
                    string cmdText = string.Format("select * from {0}", tableName);
                    if (!string.IsNullOrEmpty(whereSql))
                    {
                        cmdText = cmdText + " where " + whereSql;
                    }
                    cmd.CommandText = cmdText;
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        int count = reader.FieldCount;
                        while (reader.Read())
                        {
                            T obj = (T)Activator.CreateInstance(typeof(T));
                            for (int i = 0; i < count; i++)
                            {
                                string name = reader.GetName(i);
                                PropertyInfo property = item.GetPropertyInfo(name);
                                if (property != null)
                                {
                                    if (!reader.IsDBNull(i))
                                    {
                                        object value = reader.GetValue(i);
                                        property.SetValue(obj, value, null);
                                    }
                                }
                            }
                            list.Add(obj);
                        }
                    }
                }
            }
            finally
            {
                Close(false);
            }
            return list;
        }

        public virtual int Update<T>(T obj, params string[] keys)
        {
            if (keys == null || keys.Length == 0)
            {
                throw new ArgumentException("update method : table key(s) cann't be null or empty.");
            }
            int affect = 0;
            try
            {
                Open();
                SchemaCacheItem item = DBMapSchemaCache.GetCacheItem(typeof(T));
                using (IDbCommand cmd = _dbConn.CreateCommand())
                {
                    StringBuilder setBuilder = new StringBuilder(string.Empty);
                    StringBuilder whereBuilder = new StringBuilder(string.Empty);
                    foreach (string columnName in item.ColumnNameCollection)
                    {
                        ColumnMapAttribute colMap = item.GetColumnMap(columnName);
                        PropertyInfo info = item.GetPropertyInfo(columnName);
                        if (info != null && colMap != null && info.GetValue(obj, null) != null)
                        {
                            if (!keys.Contains(colMap.ColumnName, StringComparer.CurrentCultureIgnoreCase))
                            {
                                setBuilder.Append(string.Format("{0}=@{0} ,", colMap.ColumnName));
                            }
                            else
                            {
                                whereBuilder.Append(string.Format("{0}=@{0} and ", colMap.ColumnName));
                            }
                            DbParameter dbPar = _factory.CreateParameter();
                            dbPar.ParameterName = "@" + colMap.ColumnName;
                            dbPar.Value = info.GetValue(obj, null);
                            cmd.Parameters.Add(dbPar);
                        }
                    }
                    string setSql = setBuilder.ToString();
                    setSql = setSql.Substring(0, setSql.Length - 1);
                    string whereSql = whereBuilder.ToString();
                    whereSql = whereSql.Substring(0, whereSql.Length - 4);
                    string updateSql = string.Format("UPDATE {0} SET {1} where {2}", item.TableMap.TableName, setSql, whereSql);

                    cmd.CommandText = updateSql;
                    affect = cmd.ExecuteNonQuery();

                }
            }
            finally
            {
                Close(false);
            }
            return affect;
        }

        public virtual int Insert<T>(T obj, out long insertedIdentity)
        {
            insertedIdentity = -1;
            int affect = 0;
            try
            {
                Open();
                SchemaCacheItem item = DBMapSchemaCache.GetCacheItem(typeof(T));
                using (IDbCommand cmd = _dbConn.CreateCommand())
                {
                    string colSql = string.Empty;
                    string valSql = string.Empty;

                    foreach (string columnName in item.ColumnNameCollection)
                    {
                        ColumnMapAttribute colMap = item.GetColumnMap(columnName);
                        PropertyInfo info = item.GetPropertyInfo(columnName);
                        if (info != null && colMap != null && !colMap.IsIdentity && info.GetValue(obj, null) != null)
                        {
                            colSql += string.Format("{0},", colMap.ColumnName);
                            valSql += string.Format("@{0},", colMap.ColumnName);
                            cmd.Parameters.Add(CreateParam(colMap.ColumnName, info.GetValue(obj, null)));
                        }
                    }
                    colSql += "IsDeleted";
                    valSql += "@IsDeleted";
                    cmd.Parameters.Add(CreateParam("IsDeleted", false));
                    string insertSql = string.Format("insert into {0}({1}) values({2})", item.TableMap.TableName, colSql, valSql);

                    cmd.CommandText = insertSql;
                    affect = cmd.ExecuteNonQuery();

                    cmd.Parameters.Clear();
                    cmd.CommandText = "select @@IDENTITY";
                    object lastedId = cmd.ExecuteScalar();
                    if (lastedId != null)
                    {
                        long.TryParse(lastedId.ToString(), out insertedIdentity);
                    }
                }
            }
            finally
            {
                Close(false);
            }
            return affect;
        }

        public virtual int Insert<T>(T obj)
        {
            long nouse;
            return Insert(obj, out nouse);
        }

        public virtual int Delete<T>(params DBParam[] keys)
        {
            if (keys == null || keys.Length == 0)
            {
                throw new ArgumentException("delete method : table key(s) cann't be null or empty.");
            }
            int affect = 0;
            try
            {
                Open();
                using (IDbCommand cmd = _dbConn.CreateCommand())
                {
                    string whereSql = string.Empty;
                    SchemaCacheItem item = DBMapSchemaCache.GetCacheItem(typeof(T));
                    foreach (DBParam pair in keys)
                    {
                        string columnName = pair.Key;
                        ColumnMapAttribute colMap = item.GetColumnMap(columnName);
                        whereSql += string.Format("{0}=@{0} and ", colMap.ColumnName);
                        cmd.Parameters.Add(CreateParam(colMap.ColumnName, pair.Value));
                    }
                    whereSql = whereSql.Substring(0, whereSql.Length - 4);
                    string delSql = string.Empty;
                    if (_realDelete)
                    {
                        delSql = string.Format("DELETE FROM {0} where {1}", item.TableMap.TableName, whereSql);
                    }
                    else
                    {
                        delSql = string.Format("UPDATE {0} SET IsDeleted = 'true' where {1}", item.TableMap.TableName, whereSql);
                    }
                    cmd.CommandText = delSql;
                    affect = cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                Close(false);
            }
            return affect;
        }

        public virtual int Delete<T>(T obj, params string[] keys)
        {
            if (keys == null || keys.Length == 0)
            {
                throw new ArgumentException("delete method : table key(s) cann't be null or empty.");
            }
            int affect = 0;
            try
            {
                Open();
                using (IDbCommand cmd = _dbConn.CreateCommand())
                {
                    string whereSql = string.Empty;
                    SchemaCacheItem item = DBMapSchemaCache.GetCacheItem(typeof(T));
                    foreach (string columnName in keys)
                    {
                        ColumnMapAttribute colMap = item.GetColumnMap(columnName);
                        PropertyInfo info = item.GetPropertyInfo(columnName);
                        whereSql += string.Format("{0}=@{0} and ", colMap.ColumnName);
                        cmd.Parameters.Add(CreateParam(colMap.ColumnName, info.GetValue(obj, null)));
                    }

                    whereSql = whereSql.Substring(0, whereSql.Length - 4);
                    string delSql = string.Empty;
                    if (_realDelete)
                    {
                        delSql = string.Format("DELETE FROM {0} where {1}", item.TableMap.TableName, whereSql);
                    }
                    else
                    {
                        delSql = string.Format("UPDATE {0} SET IsDeleted = 'true' where {1}", item.TableMap.TableName, whereSql);
                    }
                    cmd.CommandText = delSql;
                    affect = cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                Close(false);
            }
            return affect;
        }

        public void Dispose()
        {
            Close(true);
        }

        protected virtual object Excute(string sql)
        {
            object result = null;
            try
            {
                Open();
                using (IDbCommand cmd = this._dbConn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    result = cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                Close(false);
            }
            return result;
        }

        private DbParameter CreateParam(string fieldName, object fieldValue)
        {
            DbParameter dbPar = _factory.CreateParameter();
            dbPar.ParameterName = "@" + fieldName;
            dbPar.Value = fieldValue;
            return dbPar;
        }

        #region 父类抽象方法
        protected abstract List<T> Select<T>(string sql);
        protected abstract int Update(string sql);
        protected abstract int Delete(string sql);
        protected abstract int Insert(string sql);

        #endregion
    }
}